<?php
/**
 * Модель каталога автозапчастей
 *
 * @author Алексей
 */
class Catalog
{	
	/**
	 * Возвращает список марок автомобилей
	 * @return type
	 */
	public static function getManufacturers()
	{
		$res = Yii::app()->db->createCommand()
			->select()
			->from('{{manufacturers}}')
			->order('MFA_BRAND')
			->queryAll();
		return CHtml::listData($res, 'MFA_ID', 'MFA_BRAND');
	}
	
	/**
	 * Возвращает список типов топлива
	 * @return type
	 */
	public static function getFuelTypes()
	{
		$cmd = Yii::app()->db->createCommand();
		$fuelTypes = $cmd->select()
			->from('{{types}}')
			->leftJoin('{{designations}}', 'TYP_KV_FUEL_DES_ID=DES_ID')
			->leftJoin('{{des_texts}}', 'DES_TEX_ID=TEX_ID')
			->where('TYP_KV_FUEL_DES_ID!=0')
			->order('TEX_TEXT')
			->queryAll();
		return CHtml::listData($fuelTypes, 
			'TYP_KV_FUEL_DES_ID', 'TEX_TEXT');
	}
	
	/**
	 * Возвращает список моделей по марке автомобиля
	 */
	public static function getModels($MFA_ID)
	{
		$cmd = Yii::app()->db->createCommand();		
		$models = $cmd->select('MOD_ID, TEX_TEXT')
			->from('{{models}}')
			->join('{{country_designations}}', 'MOD_CDS_ID=CDS_ID')
			->join('{{des_texts}}', 'CDS_TEX_ID=TEX_ID')
			->where('MOD_MFA_ID=:MFA_ID', array(':MFA_ID' => $MFA_ID))
			->order('{{des_texts}}.TEX_TEXT ASC')
			->queryAll();
		return $models;
	}
	
	/**
	 * Возвращает список типов ТС для модели с ID $MOD_ID
	 */
	public function getTypes($MOD_ID)
	{
		$models = Yii::app()->db->createCommand()
			->select(array(
				'MFA_BRAND', 
				'mod_txt.TEX_TEXT AS MOD_TEXT',
				'typ_des_texts.TEX_TEXT AS TYP_TEXT',
				'LEFT(TYP_PCON_START, 4) AS YEAR_START',
				'LEFT(TYP_PCON_END, 4) AS YEAR_END',
				'TYP_HP_FROM',
				'TYP_LITRES',
				'fuel_txt.TEX_TEXT AS FUEL',
				'TYP_ID AS ID',
			))
			->from('{{types}}')
			->join('{{models}}', 'MOD_ID=TYP_MOD_ID')
			->join('{{manufacturers}}', 'MOD_MFA_ID=MFA_ID')
			->leftJoin('{{country_designations}} AS typ_cds_t', 'TYP_CDS_ID=typ_cds_t.CDS_ID')
			->leftJoin('{{country_designations}} AS mod_cds_t', 'MOD_CDS_ID=mod_cds_t.CDS_ID')
			->leftJoin('{{designations}} AS fuel_des_t', 'TYP_KV_FUEL_DES_ID=fuel_des_t.DES_ID')
			->leftJoin('{{des_texts}} AS fuel_txt', 'fuel_des_t.DES_TEX_ID=fuel_txt.TEX_ID')
			->leftJoin('{{des_texts}} AS typ_des_texts', 'typ_cds_t.CDS_TEX_ID=typ_des_texts.TEX_ID')
			->leftJoin('{{des_texts}} AS mod_txt', 'mod_cds_t.CDS_TEX_ID=mod_txt.TEX_ID')
			->where('TYP_MOD_ID=:MOD_ID', array(':MOD_ID' => $MOD_ID))
			->order('mod_txt.TEX_TEXT')
			->queryAll();
		return $models;
	}
	
	/**
	 * Возвращает все детали, удовлетворяющие фильтру $where
	 */
	public static function getSpares($limit, $offset=0, $where=array())
	{
		set_time_limit(0);
		Yii::app()->db->createCommand('SET SQL_BIG_SELECTS=1')->execute();
		
		$cmd = Yii::app()->db->createCommand();
		$cmd = $cmd->selectDistinct('ART_ID')
			->from('{{link_la_typ}}')
			->join('{{link_art}}', 'LA_ID = LAT_LA_ID')
			->join('{{link_ga_str}}', 'LGS_GA_ID = LAT_GA_ID')
			->join('{{articles}}', 'ART_ID = LA_ART_ID')
			->limit($limit, $offset);
			
		if(count($where) > 0){
			foreach($where as $value){
				$cmd = $cmd->where($where);
			}
		}
		$results = $cmd->queryAll();
		
		$articles = array();
		foreach($results as $row)
		{
			$articles[] = $row['ART_ID'];
		}
		
		$results = array();
		$results['articles'] = Yii::app()->db->createCommand()
			->select(array(
				'ART_ID',
				'ART_ARTICLE_NR',
				'art_des_texts_RU.TEX_TEXT AS TEX_TEXT_RU',
				'art_des_texts_EN.TEX_TEXT AS TEX_TEXT_EN',
				'SUP_BRAND'
			))
			->from('{{articles}}')
			->leftJoin('{{suppliers}}', 'ART_SUP_ID=SUP_ID')
			->leftJoin('{{link_art}}', 'ART_ID=LA_ART_ID')
			->leftJoin('{{generic_articles}}', 'GA_ID=LA_GA_ID')
			->leftJoin('{{designations}} AS art_des_t_RU', 'ART_COMPLETE_DES_ID=art_des_t_RU.DES_ID AND art_des_t_RU.DES_LNG_ID=16')
			->leftJoin('{{des_texts}} AS art_des_texts_RU', 'art_des_t_RU.DES_TEX_ID=art_des_texts_RU.TEX_ID')
			->leftJoin('{{designations}} AS art_des_t_EN', 'GA_DES_ID=art_des_t_EN.DES_ID AND art_des_t_EN.DES_LNG_ID=16')
			->leftJoin('{{des_texts}} AS art_des_texts_EN', 'art_des_t_EN.DES_TEX_ID=art_des_texts_EN.TEX_ID')
			->where(array('in', 'ART_ID', $articles))
			->group('ART_ID')
			->order('GA_ID')
			->queryAll();
		
		// Цены
		$cmd = Yii::app()->db->createCommand();
		$results['prices'] = $cmd->select()
			->from('{{price_types}}')
			->leftJoin('{{prices}}', 'PRI_PTP_ID=PTP_ID')
			->where(array('in', 'PRI_ART_ID', $articles))
			->queryAll();
		$prices = array();
		foreach($results['prices'] as $price)
		{
			$prices[$price['PRI_ART_ID']][] = $price;
		}
		$results['prices'] = $prices;
		
		
		return $results;
	}
	
	/**
	 * Поиск деталей по ID типа ТС
	 * 
	 */
	public static function getSparesByTypeID($typeId)
	{
		set_time_limit(0);
		Yii::app()->db->createCommand('SET SQL_BIG_SELECTS=1')->execute();
		
		$cmd = Yii::app()->db->createCommand();
		$results = $cmd->selectDistinct('ART_ID')
			->from('{{link_la_typ}}')
			->join('{{link_art}}', 'LA_ID = LAT_LA_ID')
			->join('{{link_ga_str}}', 'LGS_GA_ID = LAT_GA_ID')
			->join('{{articles}}', 'ART_ID = LA_ART_ID')
			->where('LAT_TYP_ID=:LAT_TYP_ID', array(
				':LAT_TYP_ID' => $typeId
			))
			->queryAll();
		$data = array();
		foreach($results as $row)
		{
			$data[] = $row['ART_ID'];
		}
		$data = json_encode($data);
		
		// Сохранение ID найденных деталей в таблицу результатов поиска
		Yii::app()->db->createCommand()->insert('{{search_results}}', array(
			'articles'=>$data,
			'params'=>json_encode(array('type'=>$typeId))
		));
		return Yii::app()->db->lastInsertID;
	}
	
	/**
	 * Поиск деталей по артикулу
	 */
	public static function getSparesByCode($code)
	{
		set_time_limit(0);
		Yii::app()->db->createCommand('SET SQL_BIG_SELECTS=1')->execute();
		
		$cmd = Yii::app()->db->createCommand();
		// Преобразование поискового номера (остаются только буквы и цифры)
		$searchCode = self::getSearchNumber($code);
		
		$results = $cmd->select('ART_ID')
			->from('{{articles}}')
			->where(array('like', 'ART_SEARCH_NUMBER', '%'.$searchCode.'%')) 
			->queryAll();
		
		$data = array();
		foreach($results as $row)
		{
			$data[] = $row['ART_ID'];
		}
		$data = json_encode($data);
		
		// Сохранение ID найденных деталей в таблицу результатов поиска
		Yii::app()->db->createCommand()->insert('{{search_results}}', array(
			'articles'=>$data,
			'params'=>json_encode(
				array(
					'search_code'=>$searchCode, 
					'original_code'=>$code
				)
			)
		));
		return Yii::app()->db->lastInsertID;
	}
	
	
	/**
	 * Возвращает результаты поиска
	 */
	public static function getSearchResults($id, $limit, $offset=0)
	{
		Yii::app()->db->createCommand('SET SQL_BIG_SELECTS=1')->execute();
		
		$results = Yii::app()->db->createCommand()
			->select()
			->from('{{search_results}}')
			->where('id=:id', array(':id'=>$id))
			->queryRow();
		
		if($results)
		{
			$results['articles'] = json_decode($results['articles']);
			
			if(count($results['articles']) > 0)
			{
				// Получаем ID необходимых деталей
				$results['articles'] = array_unique($results['articles'], true);
				// Гораздо проще подсчитать количество на данном этапе
				$results['count'] = count($results['articles']);
				// Убираем лишние
				$articles = array_slice($results['articles'], $offset*$limit, $limit);
				// Достаем необходимую инфу о деталях по ID
				$results['articles'] = Yii::app()->db->createCommand()
					->select(array(
						'ART_ID',
						'ART_ARTICLE_NR',
						'art_des_texts_RU.TEX_TEXT AS TEX_TEXT_RU',
						'art_des_texts_EN.TEX_TEXT AS TEX_TEXT_EN',
						'SUP_BRAND'
					))
					->from('{{articles}}')
					->leftJoin('{{suppliers}}', 'ART_SUP_ID=SUP_ID')
					->leftJoin('{{link_art}}', 'ART_ID=LA_ART_ID')
					->leftJoin('{{generic_articles}}', 'GA_ID=LA_GA_ID')
					->leftJoin('{{designations}} AS art_des_t_RU', 'ART_COMPLETE_DES_ID=art_des_t_RU.DES_ID AND art_des_t_RU.DES_LNG_ID=16')
					->leftJoin('{{des_texts}} AS art_des_texts_RU', 'art_des_t_RU.DES_TEX_ID=art_des_texts_RU.TEX_ID')
					->leftJoin('{{designations}} AS art_des_t_EN', 'GA_DES_ID=art_des_t_EN.DES_ID AND art_des_t_EN.DES_LNG_ID=16')
					->leftJoin('{{des_texts}} AS art_des_texts_EN', 'art_des_t_EN.DES_TEX_ID=art_des_texts_EN.TEX_ID')
					->where(array('in', 'ART_ID', $articles))
					->group('ART_ID')
					->order('GA_ID')
					->queryAll();
					
				// Цены
				$cmd = Yii::app()->db->createCommand();
				$results['prices'] = $cmd->select()
					->from('{{price_types}}')
					->leftJoin('{{prices}}', 'PRI_PTP_ID=PTP_ID')
					->where(array('in', 'PRI_ART_ID', $articles))
					->queryAll();
				$prices = array();
				foreach($results['prices'] as $price)
				{
					$prices[$price['PRI_ART_ID']][] = $price;
				}
				$results['prices'] = $prices;
					
				// Если поиск производился по типу ТС,
				// получаем подробную информацию об этом типе
				$results['params'] = json_decode($results['params'], true);
				if(!empty($results['params']['type'])){
					$results['params']['type'] = Yii::app()->db->createCommand()
						->select(
							array(
								'MFA_BRAND',
								'mod_txt.TEX_TEXT AS MOD_TEXT',
								'typ_des_texts.TEX_TEXT AS TYP_TEXT',
							))
						->from('{{types}}')
						->join('{{models}}', 'TYP_MOD_ID=MOD_ID')
						->join('{{manufacturers}}', 'MOD_MFA_ID=MFA_ID')
						->leftJoin('{{country_designations}} AS mod_cds_t', 'MOD_CDS_ID=mod_cds_t.CDS_ID')
						->leftJoin('{{des_texts}} AS mod_txt', 'mod_cds_t.CDS_TEX_ID=mod_txt.TEX_ID')
						->leftJoin('{{country_designations}} AS typ_cds_t', 'TYP_CDS_ID=typ_cds_t.CDS_ID')
						->leftJoin('{{des_texts}} AS typ_des_texts', 'typ_cds_t.CDS_TEX_ID=typ_des_texts.TEX_ID')
						->where('TYP_ID=:TYP_ID', array(':TYP_ID'=>$results['params']['type']))
						->queryRow();
				}
			}
		}
		
		return $results;
	}
	
	/**
	 * Возвращает информацию о детали
	 */
	public static function getArticle($id)
	{	
		Yii::app()->db->createCommand('SET SQL_BIG_SELECTS=1')->execute();
		
		$cmd = Yii::app()->db->createCommand();
		$results = $cmd->select()
			->from('{{articles}}')
			->leftJoin('{{link_art}}', 'ART_ID=LA_ART_ID')
			->leftJoin('{{link_la_typ}}', 'LA_ID=LAT_LA_ID')
			->leftJoin('{{types}}', 'LAT_TYP_ID=TYP_ID')
			->leftJoin('{{models}}', 'TYP_MOD_ID=MOD_ID')
			->leftJoin('{{manufacturers}}', 'MOD_MFA_ID=MFA_ID')
			->leftJoin('{{designations}}', 'ART_COMPLETE_DES_ID=DES_ID')
			->leftJoin('{{des_texts}}', 'TEX_ID=DES_TEX_ID')
			->leftJoin('{{suppliers}}', 'ART_SUP_ID=SUP_ID')
			->where('ART_ID=:ART_ID', array(':ART_ID'=>$id))
			->limit(1)
			->queryRow();

		return $results;
	}
	
	/**
	 * Графика
	 */
	public static function getArticleGraphics($id)
	{
		// Достаем информацию о картинках
		$cmd = Yii::app()->db->createCommand();
		$grapics = $cmd->select()
			->from('{{link_gra_art}}')
			->leftJoin('{{graphics}}', 'LGA_GRA_ID=GRA_ID')
			->leftJoin('{{doc_types}}', 'GRA_DOC_TYPE=DOC_TYPE')
			->where('LGA_ART_ID=:LGA_ART_ID', array(':LGA_ART_ID'=>$id))
			->queryAll();
		
		
		$results = array();
		$imgPath = '/img/articles';
		foreach($grapics as $graphic)
		{
			$results[] = $imgPath . '/' 
				. $graphic['GRA_TAB_NR'] . '/' 
				. $graphic['GRA_GRD_ID'] . '.jpg';
		}
		
		return $results;
//		
//		foreach($grapics as $graphic)
//		{
//			// Каждая картинка хранит номер таблицы, в которой хранится собственно графика
//			$tableName = 'GRA_DATA_' . $graphic['GRA_TAB_NR'];
//			$cmd = Yii::app()->db->createCommand();
//			$tmp = $cmd->select()
//				->from('{{'.$tableName.'}}')
//				->where('GRD_ID=:GRD_ID', array(':GRD_ID'=>$graphic['GRA_GRD_ID']))
//				->queryRow();
//			
//			$results[] = array(
//				'EXT' => strtolower($graphic['DOC_EXTENSION']),
//				'GRAPHIC' => $tmp['GRD_GRAPHIC']
//			);
//		}
//		return $results;
	}
	
	/**
	 * Возвращает характеристики детали
	 */
	public static function getArticleCriteria($ART_ID)
	{
		$cmd = Yii::app()->db->createCommand();
		return $cmd->selectDistinct('TEX_TEXT, ACR_VALUE')
			->from('{{article_criteria}}')
			->join('{{criteria}}', 'ACR_CRI_ID=CRI_ID')
			->join('{{designations}}', 'CRI_DES_ID=DES_ID')
			->join('{{des_texts}}', 'DES_TEX_ID=TEX_ID')
			->where('ACR_ART_ID=:ART_ID and ACR_VALUE!=\'\'', array(':ART_ID'=>$ART_ID))
			->queryAll();
	}
	
	/**
	 * Возвращает цены для детали
	 */
	public static function getArticlePrices($ART_ID)
	{
		$cmd = Yii::app()->db->createCommand();
		return $cmd->select()
			->from('{{price_types}}')
			->leftJoin('{{prices}}', 'PRI_PTP_ID=PTP_ID and PRI_ART_ID=:ART_ID', array(':ART_ID'=>$ART_ID))
			->queryAll();
	}
	
	/**
	 * Возвращает список аналогов для детали
	 */
	public static function getArticleAnalogues($ART_ARTICLE_NR)
	{
		$cmd = Yii::app()->db->createCommand();
		$results = $cmd->select()
			->from('{{art_lookup}}')
			->join('{{articles}}', 'ART_ID=ARL_ART_ID')
			->leftJoin('{{designations}}', 'ART_COMPLETE_DES_ID=DES_ID')
			->leftJoin('{{des_texts}}', 'TEX_ID=DES_TEX_ID')
			->leftJoin('{{suppliers}}', 'ART_SUP_ID=SUP_ID')
			->where('ARL_SEARCH_NUMBER=:SEARCH_NUMBER AND ARL_KIND IN (1, 4)', 
				array(':SEARCH_NUMBER'=>self::getSearchNumber($ART_ARTICLE_NR)))
			->queryAll();
		
		return $results;
	}
	
	/**
	 * Возвращает последние $number добавленных деталей 
	 */
	public static function getLastArticles($number)
	{
		Yii::app()->db->createCommand('SET SQL_BIG_SELECTS=1')->execute();
		
		return Yii::app()->db->createCommand()
			->select()
			->from('{{articles}}')
			->join('{{designations}} AS art_des_t', 'ART_COMPLETE_DES_ID=art_des_t.DES_ID')
			->join('{{des_texts}} AS art_des_texts', 'art_des_t.DES_TEX_ID=art_des_texts.TEX_ID')
			->limit($number)
			->queryAll();
	}
	
	/**
	 * Предназначен для преобразования номера детали
	 * в поисковой номер (только буквы и цифры)
	 */
	private static function getSearchNumber($code)
	{
		return preg_replace('/[^\w\d]/u', '', $code);
	}
}

?>
